package my.action;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import my.jdbc.DbConUtil;
import my.jdbc.JdbcTemplate;
import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import util.DataExchangeUtil;
import bean.BfCkd;
import bean.BfCkdBean;
import bean.CkdBean;
import bean.CkdToStationLogsBean;
import bean.DeliveryBillBean;
import bean.PageParamBean;
import ehcache.EhCacheManager;

@Controller
@RequestMapping("ckd")
public class DeliveryBillToolsAction {

	private static Cache ehcache = EhCacheManager.getInstance();
	private static final Log log = LogFactory.getLog(DeliveryBillToolsAction.class);

	/**
	 * 北分中心出库单首页
	 * 
	 * @param ckdNo
	 * @param beginTime
	 * @param endTime
	 * @param nodeNo
	 * @param page
	 * @param selectType
	 * @return
	 */
	@RequestMapping("index")
	@ResponseBody
	public Object indexAction(String ckdNo, String beginTime, String endTime, String nodeNo, PageParamBean page, String selectType) {
		Map<String, Object> pageMap = new HashMap<String, Object>();
		if (StringUtils.isBlank(selectType)) {
			pageMap.put("total", 0);
			pageMap.put("rows", new ArrayList<DeliveryBillBean>());
			return pageMap;
		}

		String countSql = " select COUNT(1) FROM oss_acceptance_deliveryBill where 1=1 ";

		String sql = "SELECT DeliveryNo,PSD_ID psdId,FromDepotName,DeliveryTime,OilNo,ToNodeNo,DeliveryTemp,PlanL,PlanTon,CarNo,TranStatus FROM oss_acceptance_deliveryBill where 1=1 ";
		List<Object> fields = new ArrayList<Object>();

		if (StringUtils.isNotBlank(ckdNo)) {
			sql += " and DeliveryNo = ? ";
			countSql += " and DeliveryNo = ? ";
			fields.add(ckdNo);
		}

		if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
			sql += " and DATE_FORMAT(DeliveryTime,'%Y-%m-%d') >= ? and DATE_FORMAT(DeliveryTime,'%Y-%m-%d') <= ? ";
			countSql += " and DATE_FORMAT(DeliveryTime,'%Y-%m-%d') >= ? and DATE_FORMAT(DeliveryTime,'%Y-%m-%d') <= ? ";
			fields.add(beginTime);
			fields.add(endTime);
		}

		if (StringUtils.isNotBlank(nodeNo)) {
			sql += " and ToNodeNo = ? ";
			countSql += " and ToNodeNo = ? ";
			fields.add(nodeNo);
		}

		sql += "ORDER BY DeliveryTime DESC limit " + page.getOffset() + "," + page.getPagesize() + "";

		Object[] count = JdbcTemplate.queryFieldInfoByFields(DbConUtil.getGsmcenterCon(), countSql, fields, DbConUtil.TYPE_NAME_GSMCENTER);
		List<DeliveryBillBean> ckdList = JdbcTemplate.queryListInfoByFields(DbConUtil.getGsmcenterCon(), sql, DeliveryBillBean.class, fields, DbConUtil.TYPE_NAME_GSMCENTER);

		pageMap.put("total", count[0]);
		pageMap.put("rows", ckdList);

		ehcache.put(new Element("result", ckdList));
		return pageMap;
	}

	/**
	 * 北分中心出库单重置传输位
	 * 
	 * @param ckdNoList
	 * @return
	 */
	@RequestMapping("reset")
	@ResponseBody
	public Object resetAction(@RequestBody List<String> ckdNoList) {
		String nowTime = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss");
		for (String ckdNo : ckdNoList) {
			String sql = " UPDATE oss_acceptance_deliveryBill SET TranStatus = ? WHERE DeliveryNo = ? ";
			String log_sql = "INSERT INTO delivery_log VALUES('" + ckdNo + "','" + nowTime + "')";
			List<Object> fields = new ArrayList<Object>();
			fields.add("0");
			fields.add(ckdNo);
			JdbcTemplate.updateByFields(DbConUtil.get162testCon(), log_sql, null, DbConUtil.TYPE_NAME_162TEST);
			JdbcTemplate.updateByFields(DbConUtil.getGsmcenterCon(), sql, fields, DbConUtil.TYPE_NAME_GSMCENTER);
		}
		return true;
	}

	/**
	 * 出库单写站级库
	 * 
	 * @param ckdNoList
	 * @return
	 */
	@RequestMapping("sendckd")
	@ResponseBody
	public Object sendCkdToStationAction(@RequestBody List<String> ckdNoList) {
		// 存储异常的油站代码
		List<String> stationErrorList = new ArrayList<String>();

		for (String ckdNo : ckdNoList) {
			String sql = "SELECT ck_id,psd_id,gk_oils_id oils_id,yk_id,fh_date,fh_weight,WUNIT w_unit,fh_bulk,cp_no,cyr,test_temp,test_density,lsgc,kcdd,station_code,yk_name,xybj FROM sh_ckd WHERE ck_id = '"
					+ ckdNo + "'";
			BfCkdBean ckdInfo = JdbcTemplate.queryObjectInfoByBean(DbConUtil.get162ptCon(), sql, BfCkdBean.class, null, DbConUtil.TYPE_NAME_162PT);

			String stationCode = ckdInfo.getStation_code();
			String gkStationCode = DataExchangeUtil.getGkStationCode(stationCode);

			if (StringUtils.isBlank(stationCode)) {
				continue;
			}

			if (stationErrorList.contains(stationCode)) {
				continue;
			}

			// 查询该出库单对应的油站的IP地址
			String ipAddress = DataExchangeUtil.getIpAddressByStationCode(stationCode);
			if (StringUtils.isBlank(ipAddress)) {
				log.info("出库单下发写库:该站的IP地址未填写!stationCode: " + stationCode);
				stationErrorList.add(stationCode);
				continue;
			}

			if (!DataExchangeUtil.isOkForIpAddressCon(ipAddress)) {
				// 这个IP地址重试3次,还是连不上数据库
				log.info("出库单下发写库,连接数据库失败,油站代码:" + stationCode + ",IP地址:" + ipAddress);
				stationErrorList.add(stationCode);
				continue;
			}

			// 对日期格式进行转换
			String fhDate = "";
			DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
			try {
				Date date = format.parse(ckdInfo.getFh_date());
				fhDate = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
			} catch (ParseException e) {
				e.printStackTrace();
			}
			double density = Double.valueOf(ckdInfo.getTest_density());
			String insertStationSql = "INSERT INTO oss_acceptance_deliveryBill VALUES ('" + ckdInfo.getCk_id() + "', '" + ckdInfo.getPsd_id() + "', '" + fhDate + "', '"
					+ ckdInfo.getOils_id() + "', '" + gkStationCode + "', '', '', '" + ckdInfo.getYk_name() + "', " + ckdInfo.getTest_temp() + ", " + ckdInfo.getFh_bulk() + ", "
					+ ckdInfo.getFh_weight() + ", NULL, '" + ckdInfo.getCp_no() + "', '', NULL, '', '0', '0', '', " + density + ") ";
			// 将出库单写入站级的数据库
			boolean isOK = JdbcTemplate.insertByBean(DbConUtil.getGsmappCon(ipAddress), insertStationSql, null, ipAddress);
			if (isOK) {
				// 重置北分的传输标示
				resetBFCenterTranStatus(ckdInfo.getCk_id());
			}

		}
		return true;
	}

	/**
	 * 重置中心平台出库单的传输状态
	 * 
	 * @param ckdNoList
	 * @return
	 */
	@RequestMapping("resetbf")
	@ResponseBody
	public Object resetBfAction(@RequestBody List<String> ckdNoList) {

		for (String ckdNo : ckdNoList) {
			String updateSql = "UPDATE sh_ckd SET SEND_STATUS = '0' WHERE ck_id = '" + ckdNo + "'";
			JdbcTemplate.updateByBean(DbConUtil.get162ptCon(), updateSql, null, DbConUtil.TYPE_NAME_162PT);
		}
		return true;
	}

	/**
	 * 中心平台出库单首页
	 * 
	 * @param ckdNo
	 * @param beginTime
	 * @param endTime
	 * @param nodeName
	 * @param page
	 * @param selectType
	 * @return
	 */
	@RequestMapping("indexbf")
	@ResponseBody
	public Object indexBfAction(String ckdNo, String beginTime, String endTime, String nodeName, PageParamBean page, String selectType) {
		Map<String, Object> pageMap = new HashMap<String, Object>();
		if (StringUtils.isBlank(selectType)) {
			pageMap.put("total", 0);
			pageMap.put("rows", new ArrayList<CkdBean>());
			return pageMap;
		}
		String countSql = " SELECT COUNT(1) FROM SH_CKD c LEFT JOIN SH_STATION s ON c.station_code = s.station_code  WHERE 1=1 ";
		String sql = "SELECT * FROM ( SELECT row_number () OVER (ORDER BY c.ck_id ASC) rowId,c.ck_id ckId,c.psd_id psdId,c.gh,c.yk_name ykName,s.station_name stationName,c.fh_date fhDate,c.xybj xybj,c.send_status sendStatus FROM SH_CKD c LEFT JOIN SH_STATION s ON c.station_code = s.station_code  WHERE 1=1 ";
		List<Object> fields = new ArrayList<Object>();
		if (StringUtils.isNotBlank(ckdNo)) {
			sql += " and c.ck_id = ? ";
			countSql += " and c.ck_id = ? ";
			fields.add(ckdNo);
		}
		if (StringUtils.isNotBlank(nodeName)) {
			sql += " AND s.station_name LIKE '%" + nodeName + "%' ";
			countSql += " AND s.station_name LIKE '%" + nodeName + "%' ";
		}

		if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
			String btime = beginTime.replaceAll("-", "");
			String etime = endTime.replaceAll("-", "");
			sql += " AND SUBSTRING (c.fh_date, 1, 8) >= '" + btime + "' AND SUBSTRING (c.fh_date, 1, 8) <= '" + etime + "' ";
			countSql += " AND SUBSTRING (c.fh_date, 1, 8) >= '" + btime + "' AND SUBSTRING (c.fh_date, 1, 8) <= '" + etime + "' ";
		}
		sql += " ) AS t WHERE t.rowId BETWEEN " + (page.getOffset() + 1) + "  AND " + page.getPage() * page.getPagesize() + " ";
		List<CkdBean> ckdList = JdbcTemplate.queryListInfoByFields(DbConUtil.get162ptCon(), sql, CkdBean.class, fields, DbConUtil.TYPE_NAME_162PT);
		Object[] count = JdbcTemplate.queryFieldInfoByFields(DbConUtil.get162ptCon(), countSql, fields, DbConUtil.TYPE_NAME_162PT);
		pageMap.put("total", count[0]);
		pageMap.put("rows", ckdList);
		return pageMap;
	}

	/**
	 * 出库单写入站级库接口
	 * 
	 * @param bfCkdList
	 * @return
	 */
	@RequestMapping("sendCkdToStation")
	@ResponseBody
	public Object sendCkdToStation(@RequestBody List<BfCkd> bfCkdList) {
		try {
			// List<BfCkd> bfCkdList = mapper.readValue(params, new TypeReference<List<BfCkd>>() {
			// });

			log.info("平台调用出库单写库: 总数据: " + bfCkdList.size() + "条..");

			List<String> stationErrorList = new ArrayList<String>();
			for (BfCkd bfCkd : bfCkdList) {
				String gkStationCode = bfCkd.getNodeno();
				String appStationCode = DataExchangeUtil.getAppStationCode(gkStationCode);
				if (StringUtils.isBlank(appStationCode)) {
					continue;
				}
				if (stationErrorList.contains(appStationCode)) {
					continue;
				}
				String ipAddress = DataExchangeUtil.getIpAddressByStationCode(appStationCode);

				if (StringUtils.isBlank(ipAddress)) {
					log.info("平台调用出库单写库:该站的IP地址未填写!stationCode: " + appStationCode);
					stationErrorList.add(appStationCode);
					continue;
				}

				if (!DataExchangeUtil.isOkForIpAddressCon(ipAddress)) {
					// 这个IP地址重试3次,还是连不上数据库
					log.info("平台调用出库单写库,连接数据库失败,油站代码: " + appStationCode + ",IP地址: " + ipAddress);
					stationErrorList.add(appStationCode);
					continue;
				}

				// IP和油站代码是否一致
				boolean isRightIPForStation = DataExchangeUtil.isOkForStationAndIP(ipAddress, gkStationCode);
				if (!isRightIPForStation) {
					log.info("平台调用出库单写库,IP地址维护错误,管控油站代码: " + gkStationCode + ",IP地址: " + ipAddress);
					stationErrorList.add(appStationCode);
					continue;
				}

				String insertStationSql = "INSERT INTO oss_acceptance_deliveryBill VALUES ('" + bfCkd.getCK_ID() + "', '" + bfCkd.getPSD_ID() + "', '" + bfCkd.getFH_DATE()
						+ "', '" + bfCkd.getOILS_ID() + "', '" + gkStationCode + "', '', '', '" + bfCkd.getYK_ID() + "', " + bfCkd.getTEST_TEMP() + ", " + bfCkd.getFH_BULK()
						+ ", " + bfCkd.getFH_WEIGHT() + ", NULL, '" + bfCkd.getCP_NO() + "', '', NULL, '', '0', '0', '', " + bfCkd.getTEST_DENSITY() + ") ";

				// 查询该站是否已经存在该出库单
				String ckdSql = " select 1 from oss_acceptance_deliveryBill where DeliveryNo = '" + bfCkd.getCK_ID() + "' ";
				Object[] ckdInfo = JdbcTemplate.queryFieldInfoByBean(DbConUtil.getGsmappCon(ipAddress), ckdSql, null, ipAddress);
				if (ckdInfo != null) {
					// log.info("平台调用出库单写库,油站IP: " + ipAddress + ", 该油站已经存在该出库单,出库单ID: " +
					// bfCkd.getCK_ID());
					resetBFCenterTranStatus(bfCkd.getCK_ID());
				} else {
					boolean isInsertOk = JdbcTemplate.insertByBean(DbConUtil.getGsmappCon(ipAddress), insertStationSql, null, ipAddress);
					if (isInsertOk) {
						log.info("平台调用出库单写库,油站IP: " + ipAddress + ", 该出库单插入成功,出库单ID: " + bfCkd.getCK_ID());
						resetBFCenterTranStatus(bfCkd.getCK_ID());
						insertAppJKCkdLogs(bfCkd, ipAddress, "1");
					} else {
						log.info("平台调用出库单写库,油站IP: " + ipAddress + ", 该出库单插入失败,出库单ID: " + bfCkd.getCK_ID());
						insertAppJKCkdLogs(bfCkd, ipAddress, "0");
					}
				}
			}

		} catch (Exception e) {
			log.info("平台调用出库单写库: " + e);
			return false;
		}

		return true;
	}

	/**
	 * 出库单写站级库日志
	 * 
	 * @param bfCkd
	 * @param ipAddress
	 * @param isOk
	 */
	private void insertAppJKCkdLogs(BfCkd bfCkd, String ipAddress, String isOk) {
		// 记录日志
		String insertTime = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss");
		String logSql = " INSERT INTO appjk_ckd_logs VALUES ('" + bfCkd.getCK_ID() + "','" + ipAddress + "','" + isOk + "','" + insertTime + "') ";
		JdbcTemplate.insertByBean(DbConUtil.get162testCon(), logSql, null, DbConUtil.TYPE_NAME_162TEST);
	}

	private void resetBFCenterTranStatus(String ckdId) {
		// 将北分中心的出库单的传输状态 置为 1
		String sql = " UPDATE oss_acceptance_deliveryBill SET TranStatus = '1' WHERE DeliveryNo = '" + ckdId + "' ";
		JdbcTemplate.updateByBean(DbConUtil.getGsmcenterCon(), sql, null, DbConUtil.TYPE_NAME_GSMCENTER);
	}

	/**
	 * 查询出库单写站级库的日志
	 * 
	 * @param ckdNo
	 * @param beginTime
	 * @param endTime
	 * @param ipAddress
	 * @param page
	 * @return
	 */
	@RequestMapping("selectCkdToStationLogs")
	@ResponseBody
	public Map<String, Object> selectCkdToStationLogs(String ckdNo, String beginTime, String endTime, String ipAddress, PageParamBean page) {
		Map<String, Object> pageMap = new HashMap<String, Object>();
		String countSql = " SELECT COUNT(1) FROM appjk_ckd_logs WHERE 1=1 ";
		String sql = " SELECT * FROM ( SELECT row_number() OVER (ORDER BY inserttime DESC) rowId,ckdId,ip,isok,inserttime FROM appjk_ckd_logs WHERE 1=1 ";
		if (StringUtils.isNotBlank(ckdNo)) {
			sql += " AND ckdId = '" + ckdNo + "' ";
			countSql += " AND ckdId = '" + ckdNo + "' ";
		}
		if (StringUtils.isNotBlank(ipAddress)) {
			sql += " AND ip = '" + ipAddress + "' ";
			countSql += " AND ip = '" + ipAddress + "' ";
		}

		if (StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)) {
			sql += " AND CONVERT(VARCHAR, inserttime, 23) >= '" + beginTime + "' AND CONVERT(VARCHAR, inserttime, 23) <= '" + endTime + "' ";
			countSql += " AND CONVERT(VARCHAR, inserttime, 23) >= '" + beginTime + "' AND CONVERT(VARCHAR, inserttime, 23) <= '" + endTime + "' ";
		}
		sql += " ) AS t WHERE t.rowId BETWEEN " + (page.getOffset() + 1) + "  AND " + page.getPage() * page.getPagesize() + " ";
		List<CkdToStationLogsBean> jhysLogList = JdbcTemplate.queryListInfoByFields(DbConUtil.get162testCon(), sql, CkdToStationLogsBean.class, null, DbConUtil.TYPE_NAME_162TEST);
		Object[] count = JdbcTemplate.queryFieldInfoByFields(DbConUtil.get162testCon(), countSql, null, DbConUtil.TYPE_NAME_162TEST);
		pageMap.put("total", count[0]);
		pageMap.put("rows", jhysLogList);
		return pageMap;
	}

}
